package h5

import (
	. "chess_platform/internal/common"
	"chess_platform/models/client"
	"fmt"
	"github.com/astaxie/beego/orm"
	"strings"
	"time"
)

func NewUser(user map[string]interface{}, multiOrm ...orm.Ormer) (int64, error) {
	o := NewOrm(multiOrm, DBChessCenter)
	values, sql := InsertSql(TableH5User, user)
	result, err := o.Raw(sql, values).Exec()
	if err != nil {
		return 0, err
	}
	return result.LastInsertId()
}

func UpdateUser(user map[string]interface{}, uid int64, multiOrm ...orm.Ormer) (int64, error) {
	o := NewOrm(multiOrm, DBChessCenter)
	condition := fmt.Sprintf(`uid = %v`, uid)
	values, sql := UpdateSql(TableH5User, user, condition)
	result, err := o.Raw(sql, values).Exec()
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

//只能更新一级代理的所属区域，注意，只能更新一级代理的,二级代理的随着一级代理改变而改变
func UpdateUserGroupId(groupId, uid int64) (int64, error) {
	o := NewDBOrm(DBChessCenter)
	o.Begin()
	defer o.Rollback()
	sqlUid := fmt.Sprintf(`UPDATE %v SET group_id =  %v WHERE uid = %v`, TableH5User, groupId, uid)
	result, err := o.Raw(sqlUid).Exec()
	if err != nil {
		o.Rollback()
		return 0, err
	}
	sqlPid := fmt.Sprintf(`UPDATE %v SET group_id =  %v WHERE pid = %v`, TableH5User, groupId, uid)
	result, err = o.Raw(sqlPid).Exec()
	if err != nil {
		o.Rollback()
		return 0, err
	}
	o.Commit()
	return result.RowsAffected()
}

//只能更新二级代理所属的推广员
func UpdateAgentRelation(uid, oldPid, newPid, newGroupId int64) (int64, error) {
	o := NewDBOrm(DBChessCenter)
	o.Begin()
	defer o.Rollback()
	//改变二级代理的大区及所属推广员
	data := map[string]interface{}{
		"group_id": newGroupId,
		"pid":      newPid,
	}
	_, err := UpdateUser(data, uid)
	if err != nil {
		o.Rollback()
		return 0, err
	}
	//减去新推广员一个代理名额
	_, err = UpdateUserAgentNum(-1, newPid, o)
	if err != nil {
		o.Rollback()
		return 0, err
	}
	//还回旧推广员一个代理名额
	n, err := UpdateUserAgentNum(1, oldPid, o)
	if err != nil {
		o.Rollback()
		return 0, err
	}
	o.Commit()
	return n, err
}

//更新一级代理的开通代理的名额
func UpdateUserAgentNum(agentNum, uid int64, multiOrm ...orm.Ormer) (int64, error) {
	o := NewOrm(multiOrm, DBChessCenter)
	sql := fmt.Sprintf(`UPDATE %v SET agent_num = agent_num + %v WHERE uid = %v`, TableH5User, agentNum, uid)
	result, err := o.Raw(sql).Exec()
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

func UpdateUserByCondition(user map[string]interface{}, condition string, multiOrm ...orm.Ormer) (int64, error) {
	o := NewOrm(multiOrm, DBChessCenter)
	values, sql := UpdateSql(TableH5User, user, condition)
	result, err := o.Raw(sql, values).Exec()
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

//新增代理登录日志
func NewLoginLog(data map[string]interface{}) (int64, error) {
	o := NewDBOrm(DBChessLog)
	values, sql := InsertSql(TableAgentLoginLog, data)
	result, err := o.Raw(sql, values).Exec()
	if err != nil {
		return 0, err
	}
	return result.LastInsertId()
}

//24小时没处理申请自动变为拒绝
//true--未到24小时
//false--已到24小时
func HandleAgentStatus(createTime, uid, pid int64, isRegisterStatus bool) (bool, error) {
	if isRegisterStatus && time.Now().Unix()-createTime >= 86400 { //如果仅仅是注册成功就没有24小时自动拒绝概念
		user := map[string]interface{}{
			"create_time": time.Now().Unix(), //超过了24小时，就把注册成功改为现在
		}
		_, err := UpdateUser(user, uid)
		if err != nil {
			return true, err
		}
		return true, nil
	}
	if time.Now().Unix()-createTime >= 86400 { //超过24小时86400 = 24 * 3600
		user := map[string]interface{}{
			"status": VerifyNotPass,
		}
		if pid != 0 { //开通二级代理
			o := NewDBOrm(DBChessCenter)
			o.Begin()
			defer o.Rollback()
			//更新被审核的代理状态
			_, err := UpdateUser(user, uid, o)
			if err != nil {
				o.Rollback()
				return true, err
			}
			//如果审核失败，就还原一个开通代理的名额
			//更新上级代理数加1
			_, err = UpdateUserAgentNum(1, pid, o)
			if err != nil {
				o.Rollback()
				return true, err
			}
			o.Commit()
			return false, nil
		} else { //开通一级代理
			_, err := UpdateUser(user, uid)
			if err != nil {
				return true, err
			}
			return false, nil
		}
	}
	return true, nil
}

//判断一级代理当月是否考核通过了
func Level1AgentIsPass(uid int64) bool {
	if !AgentCheck {
		return true //代理考核功能不开启
	}
	Key := fmt.Sprintf("%v%v", KeyH5AgentEvaluation, uid)
	if ok, _ := Redis.Exists(Key); ok { //存在cache
		ok, _ = Redis.GetBool(Key)
		return ok
	}

	//不存在,要从mysql数据库获取
	field := []string{"is_pass", "pass_time", "agent_time"}
	u, err := SpecifyUserInfoById(uid, field)
	if err != nil || u == nil {
		LogH5.Error(fmt.Sprintf("代理:%d查找出错,所以代理默认为考核不通过", uid))
		return false
	}
	timeOut := GetTimeFromMonthEnd()

	//第一个月不算考核,第二月也不算,第三个月要考核第二个月的
	if IsCurrentMonth(u.AgentTime) || IsLastMonth(u.AgentTime) {
		Redis.SetEX(Key, timeOut, 1)
		return true
	}

	//第三个月开始考核上一个月的
	if IsCurrentMonth(u.PassTime) { //说明当月已经考核过了，无需再考核表，只需直接返回是什么就什么
		if u.IsPass == 1 {
			Redis.SetEX(Key, timeOut, 1)
			return true
		} else if u.IsPass == 0 {
			Redis.SetEX(Key, timeOut, 0)
			return false
		}
	}
	//要考核,判断上个月的一级代理是否进货量是否符合要求
	var buy int64
	sql := fmt.Sprintf(`
		SELECT
			buy
		FROM
			%v
		WHERE
			create_time = %v AND agent_id = %v
	`, TableMonthAgentLevel1, LastMonth(), uid)
	o := NewDBOrm(DBChessLog)
	err = o.Raw(sql).QueryRow(&buy)
	if err != nil {
		LogClient.Error(err.Error())
		return false
	}
	rule, err := RuleByType(RuleCardNum)
	data := map[string]interface{}{
		"pass_time": time.Now().Unix(), //考核时间
	}
	if buy < rule.Amount {
		data["is_pass"] = 0 //不通过
		client.UpdateUser(data, uid)
		Redis.SetEX(Key, timeOut, 0)
		return false
	}
	data["is_pass"] = 1 //通过
	client.UpdateUser(data, uid)
	Redis.SetEX(Key, timeOut, 1)
	return true
}

//统计一级代理列表有多少代理(去除审核不通过的)
func CountLevel2AgentNum(uid int64, multiOrm ...orm.Ormer) (int64, error) {
	sql := fmt.Sprintf(`
		SELECT
			COUNT(1)
		FROM
			%v
		WHERE
			pid = ? AND status != ?
		LIMIT 1
		`, TableH5User)
	o := NewOrm(multiOrm, DBChessCenter)
	var count int64
	err := o.Raw(sql, uid, VerifyNotPass).QueryRow(&count)
	if CheckError(err) {
		return 0, err
	}
	return count, nil
}

func UserInfoByTelephone(telephone string, multiOrm ...orm.Ormer) (*H5User, error) {
	sql := fmt.Sprintf(`
		SELECT
			uid,level,phone,status,password,login_time,frozen,frozen_reason
		FROM
			%v
		WHERE
			phone = ?
		LIMIT 1
		`, TableH5User)
	o := NewOrm(multiOrm, DBChessCenter)
	var user H5User
	err := o.Raw(sql, telephone).QueryRow(&user)
	if CheckNoExist(err) {
		return nil, nil
	}
	return &user, err
}

func SpecifyUserInfoById(uid int64, field []string, multiOrm ...orm.Ormer) (*H5User, error) {
	fieldStr := strings.Join(field, ",")
	sql := fmt.Sprintf(`
		SELECT
			%v
		FROM
			%v
		WHERE
			uid = ?
		LIMIT 1
		`, fieldStr, TableH5User)
	o := NewOrm(multiOrm, DBChessCenter)
	var user H5User
	err := o.Raw(sql, uid).QueryRow(&user)
	if CheckNoExist(err) {
		return &user, nil
	}
	return &user, err
}

//获取一级代理名下的(正式代理)二级代理列表
func ListLevel2UserById(p ListStatParams, uid int64, multiOrm ...orm.Ormer) ([]H5User, int64, error) {
	o := NewOrm(multiOrm, DBChessCenter)
	limit := (p.PageIndex - 1) * p.PageSize
	where := fmt.Sprintf(" pid = %v AND status = %v ", uid, FirstTopUpSuccess)
	if p.Name != "" {
		where = where + fmt.Sprintf(" AND nickname LIKE '%v' ", "%"+p.Name+"%")
	}
	sql := fmt.Sprintf(`SELECT COUNT(1) FROM %v WHERE %v`, TableH5User, where)
	var count int64
	err := o.Raw(sql).QueryRow(&count)
	if CheckNoExist(err) {
		return nil, 0, nil
	}
	sql = fmt.Sprintf(`
		SELECT
			uid,nickname,remark,status,head_img_url,phone,game_id,create_time
		FROM
			%v
		WHERE
			%v
		LIMIT ?,?
		`, TableH5User, where)
	var user []H5User
	_, err = o.Raw(sql, limit, p.PageSize).QueryRows(&user)
	if CheckNoExist(err) {
		return nil, 0, nil
	}
	return user, count, err
}

//获取一级代理名下的(正式代理)二级代理列表,并且排序
func ListLevel2UserAndOrder(p ListStatParams, uid int64, multiOrm ...orm.Ormer) ([]*Level2Agent, int64, error) {
	o := NewOrm(multiOrm, DBChessCenter)
	limit := (p.PageIndex - 1) * p.PageSize
	where := fmt.Sprintf(" u.pid = %v AND u.status = %v ", uid, FirstTopUpSuccess)
	if p.Name != "" {
		where = where + fmt.Sprintf(" AND u.nickname LIKE '%v' ", "%"+p.Name+"%")
	}
	sql := fmt.Sprintf(`SELECT COUNT(1) FROM %v AS u WHERE %v`, TableH5User, where)
	var count int64
	err := o.Raw(sql).QueryRow(&count)
	if CheckNoExist(err) || count == 0 {
		return nil, 0, nil
	}

	var order string
	if p.Order == 1 {
		order = "ASC"
	} else {
		order = "DESC"
	}

	if p.Type == HandleTopUp {
		sql = fmt.Sprintf(`
		SELECT
			u.uid,u.nickname,u.remark,u.status,u.head_img_url,u.phone,u.game_id,u.create_time,
			IF(SUM(cr.card_num) is NULL,0,SUM(cr.card_num)) AS handle_count
		FROM
			%v AS u
		LEFT JOIN
			%v AS cr
		ON cr.agent_id = u.uid AND cr.type = %v
		WHERE
			%v
		GROUP BY
			u.uid
		ORDER BY
			handle_count %v
		LIMIT ?,?
		`, TableH5User, TableH5CardRecord, HandleTopUp, where, order)
	} else if p.Type == AutomaticTopUp {
		sql = fmt.Sprintf(`
		SELECT
			u.uid,u.nickname,u.remark,u.status,u.head_img_url,u.phone,u.game_id,u.create_time,
			IF(SUM(cr.card_num) is NULL,0,SUM(cr.card_num)) AS automatic_count
		FROM
			%v AS u
		LEFT JOIN
			%v AS cr
		ON cr.agent_id = u.uid AND cr.type = %v
		WHERE
			%v
		GROUP BY
			u.uid
		ORDER BY
			automatic_count %v
		LIMIT ?,?
		`, TableH5User, TableH5CardRecord, AutomaticTopUp, where, order)
	} else {
		sql = fmt.Sprintf(`
		SELECT
			u.uid,u.nickname,u.remark,u.status,u.head_img_url,u.phone,u.game_id,u.create_time,
			COUNT(1) AS player_count
		FROM
			%v AS u
		LEFT JOIN
			%v AS ap
		ON ap.agent_id = u.uid
		WHERE
			%v
		GROUP BY
			u.uid
		ORDER BY
			player_count DESC
		LIMIT ?,?
		`, TableH5User, TableH5AgentPlayer, where)
	}
	var agentInfo []*Level2Agent
	_, err = o.Raw(sql, limit, p.PageSize).QueryRows(&agentInfo)
	if CheckNoExist(err) {
		return nil, 0, nil
	}
	var agentId []int64
	for _, v := range agentInfo {
		agentId = append(agentId, v.Uid)
	}
	switch p.Type {
	case HandleTopUp:
		auto, err := statLevel2AgentInfo(agentId, AutomaticTopUp, o)
		if err != nil {
			return agentInfo, count, err
		}
		player, err := statLevel2AgentInfo(agentId, 1, o)
		if err != nil {
			return agentInfo, count, err
		}
		for _, v := range agentInfo {
			v.AutomaticCount = auto[v.Uid]
			v.PlayerCount = player[v.Uid]
		}

	case AutomaticTopUp:
		handle, err := statLevel2AgentInfo(agentId, HandleTopUp, o)
		if err != nil {
			return agentInfo, count, err
		}
		player, err := statLevel2AgentInfo(agentId, 1, o)
		if err != nil {
			return agentInfo, count, err
		}
		for _, v := range agentInfo {
			v.HandleCount = handle[v.Uid]
			v.PlayerCount = player[v.Uid]
		}

	case 1:
		handle, err := statLevel2AgentInfo(agentId, HandleTopUp, o)
		if err != nil {
			return agentInfo, count, err
		}
		auto, err := statLevel2AgentInfo(agentId, AutomaticTopUp, o)
		if err != nil {
			return agentInfo, count, err
		}
		for _, v := range agentInfo {
			v.HandleCount = handle[v.Uid]
			v.AutomaticCount = auto[v.Uid]
		}
	}

	return agentInfo, count, err
}

type AgentOrder struct {
	AgentId int64 `json:"agent_id"`
	Count   int64 `json:"count"`
}

//只是统计不排序
func statLevel2AgentInfo(agentId []int64, typ int64, multiOrm ...orm.Ormer) (map[int64]int64, error) {
	agentIdStr := Join(agentId, ",")
	var sql string
	if typ == HandleTopUp {
		sql = fmt.Sprintf(`
		SELECT
			IF(SUM(card_num) is NULL,0,SUM(card_num)) AS count,agent_id
		FROM
			%v
		WHERE
			type = %d AND agent_id IN (%v)
		GROUP BY
			agent_id
	`, TableH5CardRecord, HandleTopUp, agentIdStr)
	} else if typ == AutomaticTopUp {
		sql = fmt.Sprintf(`
		SELECT
			IF(SUM(card_num) is NULL,0,SUM(card_num)) AS count,agent_id
		FROM
			%v
		WHERE
			type = %d AND agent_id IN (%v)
		GROUP BY
			agent_id
	`, TableH5CardRecord, AutomaticTopUp, agentIdStr)
	} else if typ == 1 {
		sql = fmt.Sprintf(`
		SELECT
			COUNT(1) AS count,agent_id
		FROM
			%v
		WHERE
			agent_id IN (%v)
		GROUP BY
			agent_id
	`, TableH5AgentPlayer, agentIdStr)
	}

	o := NewOrm(multiOrm, DBChessCenter)
	m := make(map[int64]int64, len(agentId))
	var agent []*AgentOrder
	_, err := o.Raw(sql).QueryRows(&agent)
	if CheckNoExist(err) {
		return m, nil
	}
	for _, v := range agent {
		m[v.AgentId] = v.Count
	}
	return m, nil
}

//统计一级代理名下的二级代理的数据,代充钻石销量，直充钻石销量,玩家数量(已经排好序了)
func CountLevel2AgentInfo(p ListStatParams, multiOrm ...orm.Ormer) ([]*Level2Agent, error) {
	agentIdStr := Join(p.AgentId, ",")
	var order string
	if p.Order == 1 {
		order = "ASC"
	} else {
		order = "DESC"
	}
	var sql string
	limit := (p.PageIndex - 1) * p.PageSize
	if p.Type == HandleTopUp {
		sql = fmt.Sprintf(`
		SELECT
			IF(SUM(card_num) is NULL,0,SUM(card_num)) AS handle_count,agent_id AS uid
		FROM
			%v
		WHERE
			type = %d AND agent_id IN (%v)
		GROUP BY
			agent_id
		ORDER BY
            handle_count %v
		LIMIT ?,?
	`, TableH5CardRecord, HandleTopUp, agentIdStr, order)
	} else if p.Type == AutomaticTopUp {
		sql = fmt.Sprintf(`
		SELECT
			IF(SUM(card_num) is NULL,0,SUM(card_num)) AS automatic_count,agent_id AS uid
		FROM
			%v
		WHERE
			type = %d AND agent_id IN (%v)
		GROUP BY
			agent_id
		ORDER BY
            automatic_count %v
		LIMIT ?,?
	`, TableH5CardRecord, AutomaticTopUp, agentIdStr, order)
	} else if p.Type == 1 {
		sql = fmt.Sprintf(`
		SELECT
			COUNT(1) AS player_count,agent_id AS uid
		FROM
			%v
		WHERE
			agent_id IN (%v)
		GROUP BY
			agent_id
		ORDER BY
			player_count
		DESC
		LIMIT ?,?
	`, TableH5AgentPlayer, agentIdStr)
	}

	o := NewOrm(multiOrm, DBChessCenter)
	var level2Agent []*Level2Agent
	_, err := o.Raw(sql, limit, p.PageSize).QueryRows(&level2Agent)
	if CheckError(err) {
		return nil, err
	}
	return level2Agent, nil
}

//统计一级代理名下的二级代理的数据,钻石销量，玩家数量
//没有排序
func CountLevel2AgentInfo2(p ListStatParams, multiOrm ...orm.Ormer) ([]*Level2Agent, error) {
	agentIdStr := Join(p.AgentId, ",")
	var sql string
	limit := (p.PageIndex - 1) * p.PageSize
	if p.Type == HandleTopUp {
		sql = fmt.Sprintf(`
		SELECT
			IF(SUM(card_num) is NULL,0,SUM(card_num)) AS handle_count,agent_id AS uid
		FROM
			%v
		WHERE
			type = %d AND agent_id IN (%v)
		GROUP BY
			agent_id
		LIMIT ?,?
	`, TableH5CardRecord, HandleTopUp, agentIdStr)
	} else if p.Type == AutomaticTopUp {
		sql = fmt.Sprintf(`
		SELECT
			IF(SUM(card_num) is NULL,0,SUM(card_num)) AS automatic_count,agent_id AS uid
		FROM
			%v
		WHERE
			type = %d AND agent_id IN (%v)
		GROUP BY
			agent_id
		LIMIT ?,?
	`, TableH5CardRecord, AutomaticTopUp, agentIdStr)
	} else if p.Type == 1 {
		sql = fmt.Sprintf(`
		SELECT
			COUNT(1) AS player_count,agent_id AS uid
		FROM
			%v
		WHERE
			agent_id IN (%v)
		GROUP BY
			agent_id
		LIMIT ?,?
	`, TableH5AgentPlayer, agentIdStr)
	}

	o := NewOrm(multiOrm, DBChessCenter)
	var level2Agent []*Level2Agent
	_, err := o.Raw(sql, limit, p.PageSize).QueryRows(&level2Agent)
	if CheckError(err) {
		return nil, err
	}
	return level2Agent, nil
}

//获取一级代理名下的还不是正式的二级代理列表
func ListManualReviewUser(p ListParams, uid int64, multiOrm ...orm.Ormer) ([]orm.Params, error) {
	where := fmt.Sprintf(" pid = %v AND status = %v", uid, BindPhoneSuccess)
	limit := (p.PageIndex - 1) * p.PageSize
	sql := fmt.Sprintf(`SELECT COUNT(1) FROM %v WHERE %v`, TableH5User, where)
	var count int64
	var err error
	var user []orm.Params
	o := NewOrm(multiOrm, DBChessCenter)
	o.Raw(sql).QueryRow(&count)
	/*
			1. status 2-未审核，3-已拒绝，4-未激活
			2. 现在需要排序是未审核--->未激活---->已拒绝
			解决方案:
		 	1. 分两部分排序，未审核的，及已拒绝和未激活的做倒序排序
			2. 有分页功能,所以要先计算出未审核的有多个条记录,然后跟limit,及limit+pageSize做比较
			3. 分三种情况:
				1. count <= limit 说明要的数据已经超了未审核的部分，所以直接返回partTwo
				2. count > limit+pageSize,说明要取的数据是在partOne部分，没有超出未审核数据范围
				3. limit < count <= limit+pageSize,说明两部分都有，
				  第一部分是未审核的limit,count
				  第二部分是partTwo的,0,limit+pageSize-count

	*/
	if count <= limit {
		user, err = listPartTwoReviewUser(uid, limit, p.PageSize)
	}
	if count > limit && count <= limit+p.PageSize {
		user1, err := listPartOneReviewUser(uid, limit, count)
		if err != nil {
			return nil, err
		}
		user2, err := listPartTwoReviewUser(uid, 0, limit+p.PageSize-count)
		if err != nil {
			return nil, err
		}
		user = append(user, user1...)
		user = append(user, user2...)
	}
	if count > limit+p.PageSize {
		user, err = listPartOneReviewUser(uid, limit, p.PageSize)
	}
	return user, err
}

type TmpUser struct {
	Uid        int64 `json:"uid"`
	CreateTime int64 `json:"create_time"`
}

//列出未审核的代理列表
func listPartOneReviewUser(uid, limit, pageSize int64, multiOrm ...orm.Ormer) ([]orm.Params, error) {
	o := NewOrm(multiOrm, DBChessCenter)
	where := fmt.Sprintf(" pid = %v AND status = %v", uid, BindPhoneSuccess)

	sql := fmt.Sprintf(`
		SELECT
			uid,nickname,status,create_time,head_img_url
		FROM
			%v
		WHERE
			%v
		LIMIT ?,?
		`, TableH5User, where)
	var user []orm.Params
	_, err := o.Raw(sql, limit, pageSize).Values(&user)
	if CheckNoExist(err) {
		return nil, nil
	}
	return user, nil
}

//列出已拒绝及未激活的代理
func listPartTwoReviewUser(uid, limit, pageSize int64, multiOrm ...orm.Ormer) ([]orm.Params, error) {
	o := NewOrm(multiOrm, DBChessCenter)
	where := fmt.Sprintf(" pid = %v AND status >= %v AND status <= %v", uid, VerifyNotPass, VerifyPass)
	sql := fmt.Sprintf(`
		SELECT
			uid,nickname,status,create_time,head_img_url
		FROM
			%v
		WHERE
			%v
		ORDER BY
			status DESC
		LIMIT ?,?
		`, TableH5User, where)
	var user []orm.Params
	_, err := o.Raw(sql, limit, pageSize).Values(&user)
	if CheckNoExist(err) {
		return nil, nil
	}
	return user, nil
}

//获取一级代理名下的还未审核的二级代理的数量
func StatManualReviewUser(pid int64, multiOrm ...orm.Ormer) (int64, error) {
	//要把绑定了授权码,手机号,及未激活的二级代理列出来检查是否超过24小时
	where := fmt.Sprintf(" pid = %v AND status IN (%v,%v,%v)",
		pid, AuthCodeSuccess, BindPhoneSuccess, VerifyPass)
	sql := fmt.Sprintf(`
		SELECT
			uid,create_time
		FROM
			%v
		WHERE
			%v
	`, TableH5User, where)
	o := NewOrm(multiOrm, DBChessCenter)
	var tmp []TmpUser
	o.Raw(sql).QueryRows(&tmp)
	for _, v := range tmp {
		HandleAgentStatus(v.CreateTime, v.Uid, pid, false)
	}

	where = fmt.Sprintf(" pid = %v AND status = %v", pid, BindPhoneSuccess)
	sql = fmt.Sprintf(`
		SELECT
			COUNT(1)
		FROM
			%v
		WHERE
			%v
	`, TableH5User, where)
	var count int64
	o.Raw(sql).QueryRow(&count)
	return count, nil
}

func IsUserExistByPhone(phone string, multiOrm ...orm.Ormer) (int64, error) {
	sql := fmt.Sprintf(`
		SELECT
			uid
		FROM
			%v
		WHERE
			phone = ?
		LIMIT 1
		`, TableH5User)
	o := NewOrm(multiOrm, DBChessCenter)
	var uid int64
	err := o.Raw(sql, phone).QueryRow(&uid)
	if CheckNoExist(err) {
		return 0, nil
	}
	return uid, err
}

func IsUserExistByOpenID(openId string, multiOrm ...orm.Ormer) (*H5User, error) {
	sql := fmt.Sprintf(`
		SELECT
			uid,level,status,login_time,frozen,frozen_reason
		FROM
			%v
		WHERE
			open_id = ?
		LIMIT 1
		`, TableH5User)
	o := NewOrm(multiOrm, DBChessCenter)
	var user H5User
	err := o.Raw(sql, openId).QueryRow(&user)
	if CheckNoExist(err) {
		return nil, nil
	}
	return &user, err
}

func IsAuthCodeExist(authCode string, multiOrm ...orm.Ormer) (AuthCode, error) {
	sql := fmt.Sprintf(`
		SELECT
			*
		FROM
			%v
		WHERE
			auth_code = ?
		LIMIT 1
		`, TableH5AuthCode)
	o := NewOrm(multiOrm, DBChessCenter)
	var ac AuthCode
	err := o.Raw(sql, authCode).QueryRow(&ac)
	if CheckNoExist(err) {
		return ac, nil
	}
	return ac, err
}

func AuthCodeInfoById(uid int64, multiOrm ...orm.Ormer) (AuthCode, error) {
	sql := fmt.Sprintf(`
		SELECT
			*
		FROM
			%v
		WHERE
			uid = ?
		LIMIT 1
		`, TableH5AuthCode)
	o := NewOrm(multiOrm, DBChessCenter)
	var authCode AuthCode
	err := o.Raw(sql, uid).QueryRow(&authCode)
	if CheckNoExist(err) {
		return authCode, nil
	}
	return authCode, err
}

func NewAuthCode(code map[string]interface{}, multiOrm ...orm.Ormer) (int64, error) {
	o := NewOrm(multiOrm, DBChessCenter)
	values, sql := InsertSql(TableH5AuthCode, code)
	result, err := o.Raw(sql, values).Exec()
	if err != nil {
		return 0, err
	}
	return result.LastInsertId()
}

//更新或创建一个新的授权码
func UpdateAuthCode(authCode map[string]interface{}, uid int64, multiOrm ...orm.Ormer) (int64, error) {
	o := NewOrm(multiOrm, DBChessCenter)
	values, sql := InsertOrUpdateSql(TableH5AuthCode, authCode)
	result, err := o.Raw(sql, values).Exec()
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

func NewOrUpdateAuthCode(authCode map[string]interface{}, multiOrm ...orm.Ormer) (int64, error) {
	o := NewOrm(multiOrm, DBChessCenter)
	values, sql := InsertOrUpdateSql(TableH5AuthCode, authCode)
	result, err := o.Raw(sql, values).Exec()
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

//获取h5方面的公告(已缓存)(不分页,反正数据也不多，直接全部返回)
func ListNotice() ([]H5Notice, error) {
	var gl []H5Notice
	err := GetCache(RedisCache, KeyH5Notice, &gl)
	if err != nil {
		sql := fmt.Sprintf(`
		SELECT
			*
		FROM
			%v
		WHERE
			publish_time <= %v
		`, TableH5Notice, time.Now().Unix())
		o := NewDBOrm(DBChessSysData)
		_, err := o.Raw(sql).QueryRows(&gl)
		if CheckError(err) {
			return nil, err
		}
		//即使为空也缓存，为了减少因为数据库里不存在此数据而步骤与数据库交互,所以是用checkError而不是checkNoExist
		SetCache(RedisCache, KeyH5Notice, gl, CacheExpire) //one day
	}
	return gl, nil
}

//h5公告
func NoticeInfoById(id int64) (*H5Notice, error) {
	var gl H5Notice
	key := fmt.Sprintf("%v%v", KeyH5NoticeId, id)
	err := GetCache(RedisCache, key, &gl)
	if err != nil {
		sql := fmt.Sprintf(`
		SELECT
			*
		FROM
			%v
		WHERE
			id = %v
		`, TableH5Notice, id)
		o := NewDBOrm(DBChessSysData)
		err := o.Raw(sql).QueryRow(&gl)
		if CheckError(err) {
			return nil, err
		}
		SetCache(RedisCache, key, gl, CacheExpire) //one day
	}
	return &gl, nil
}

//新建h5方面的反馈
func NewFeedback(data map[string]interface{}, multiOrm ...orm.Ormer) (int64, error) {
	o := NewOrm(multiOrm, DBChessCenter)
	values, sql := InsertSql(TableH5Feedback, data)
	result, err := o.Raw(sql, values).Exec()
	if err != nil {
		return 0, err
	}
	return result.LastInsertId()
}

func NewFeedbackUrl(data map[string]interface{}, multiOrm ...orm.Ormer) (int64, error) {
	o := NewOrm(multiOrm, DBChessCenter)
	values, sql := InsertSql(TableH5FeedbackUrl, data)
	result, err := o.Raw(sql, values).Exec()
	if err != nil {
		return 0, err
	}
	return result.LastInsertId()
}

//列出h5用户的反馈记录
func ListFeedbackRecord(p ListParams, uid int64) ([]orm.Params, int64, error) {
	limit := (p.PageIndex - 1) * p.PageSize
	o := NewDBOrm(DBChessCenter)
	sql := fmt.Sprintf(`
			SELECT
				*
			FROM
				%v
			WHERE
				uid = %v
			LIMIT ?,?
		`, TableH5Feedback, uid)
	var data []orm.Params
	_, err := o.Raw(sql, limit, p.PageSize).Values(&data)
	if CheckNoExist(err) {
		return nil, 0, nil
	}
	sql = fmt.Sprintf(`
		SELECT
			url
		FROM
			%v
		WHERE
			fid = ?
	`, TableH5FeedbackUrl)
	for _, v := range data {
		var url []string
		o.Raw(sql, v["id"]).QueryRows(&url)
		v["image_url"] = url
	}
	sql = fmt.Sprintf(`SELECT COUNT(1) FROM %v WHERE uid = %v `,
		TableH5Feedback, uid)
	var count int64
	err = o.Raw(sql).QueryRow(&count)
	return data, count, err
}
